package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Possession;


public class PossessionControl {
	
	/**
	 * 根据stu_id获取该学生所在寝室的所有物品
	 * @param stu_id
	 * @return
	 */
	public ArrayList<Possession> getStuPossession(int stu_id){
		DBConnect dbc = new DBConnect();
		String sql="{call getStuPossession("+stu_id+")}";
		/*
		 * String sql = "select * from possession,resource,student where " +
		 *		"possession.res_id = resource.res_id and " +
		 *		"possession.dorm_id = student.stu_dorm and " +
		 *		"student.stu_id = "+ stu_id +"";
		 */
		ArrayList<Possession> list = new ArrayList<Possession>();
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				Possession p = new Possession();
				p.setPoss_id(rs.getInt("poss_id"));
				p.setRes_name(rs.getString("res_name"));
				p.setPoss_num(rs.getInt("poss_num"));
				p.setDorm_name("dorm_name");
				list.add(p);
			}
			return list;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 根据admin_id,dorm_name,res_name获取该管理员管理的所有寝室的物资情况
	 * @param admin_id
	 * @param dorm_name
	 * @param res_name
	 * @return
	 */
	public ArrayList<Possession> getAdminPossession(int admin_id, String dorm_name, String res_name){
		DBConnect dbc = new DBConnect();
		String sql = "{call getAdminPossession("+ admin_id +", '"+ dorm_name +"', '"+res_name +"')}";
		ArrayList<Possession> list = new ArrayList<Possession>();
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				Possession p = new Possession();
				p.setPoss_id(rs.getInt("poss_id"));
				p.setRes_name(rs.getString("res_name"));
				p.setPoss_num(rs.getInt("poss_num"));
				p.setDorm_name(rs.getString("dorm_name"));
				list.add(p);
			}
			return list;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
	
	/**
	 * 更新寝室物资数量
	 * @param poss_id
	 * @param input_num
	 * @return
	 */
	public boolean updatePossession(int poss_id, int input_num) {
		DBConnect dbc = new DBConnect();
		String sql = "{call updatePossession(?,?,?)}";
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.registerOutParameter(1, java.sql.Types.BOOLEAN);
			stmt.setInt(2, poss_id);
			stmt.setInt(3, input_num);
			stmt.execute();
			return stmt.getBoolean(1);
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	/**
	 * 发放物资
	 * @param admin_id
	 * @param res_id
	 * @param num
	 * @param dorm_num
	 * @return
	 */
	public boolean distributeResource(int admin_id,int res_id,int num,int dorm_num){
		String sql = "{call distributeResource(?,?,?,?)}";
		DBConnect dbc = new DBConnect();
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.setInt(1, admin_id);
			stmt.setInt(2, res_id);
			stmt.setInt(3, num);
			stmt.setInt(4, dorm_num);
			stmt.execute();
			return true;
		}catch (SQLException e) {
			System.out.println("分派失败！");
			return false;
		}
		finally{
			dbc.close();
		}
	}
}
